﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace syc.ZhiJian
{
    public partial class 产品质保书 : System.Web.UI.Page
    {
        protected HSSFWorkbook hssfworkbook;
        protected string pinming;
        protected string gangji;
        protected string guige;
        protected string 合同号;
        protected string 总长度;
        protected string 总重量;
        protected string 总支数;
        protected string 总件数;
        protected string 订货单位;
        protected string 序号;
        protected string 炉号;
        protected string[] luhaoArray = new string[5];
        protected string[] C = new string[5];
        protected string[] Mn = new string[5];
        protected string[] Si = new string[5];
        protected string[] P = new string[5];
        protected string[] S = new string[5];
        protected string[] Cr = new string[5];
        protected string[] Ni = new string[5];
        protected string[] Cu = new string[5];
        protected string[] Mo = new string[5];
        protected string[] Al = new string[5];
        protected string[] V = new string[5];
        protected string[] Bi = new string[5];
        protected string[] Sn = new string[5];
        protected string[] Pb = new string[5];
        protected string[] As = new string[5];
        protected string[] Sb = new string[5];
        protected string[] 抗拉强度 = new string[5];
        protected string[] 屈服极限 = new string[5];
        protected string[] 伸长率 = new string[5];
        protected string[] 横向冲击 = new string[5];
        protected string[] 纵向冲击 = new string[5];
        protected string[] 通径检验 = new string[5];
        protected string[] 静水压试验 = new string[5];
        protected string[] 无损探伤 = new string[5];
        protected string[] 表面检验 = new string[5];
        protected string[] 尺寸 = new string[5];
        protected string[] 捆号 = new string[30];
        protected int count = 0;//统计炉号个数
        protected int kunhaoCount = 0;//统计捆数

        protected void Page_Load(object sender, EventArgs e)
        {
            if (RiQi.Text == "")
            {
                RiQi.Text = DateTime.Today.ToString("yyyy年M月d日");
                Button_Query_Click(null, null);
            }

        }

        protected void WriteToFile()
        {
            DateTime d;
            if (!DateTime.TryParse(this.RiQi.Text, out d))
            {
                d = DateTime.Today;
            }
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + d.ToShortDateString() + ".xls\"");
            hssfworkbook.Write(Response.OutputStream);
        }

        protected void InitializeWorkbook()
        {

            FileStream file = new FileStream(HttpContext.Current.Server.MapPath("/ZhiJian/模板/产品质量证明书.xls"), FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "产品质量证明书";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表";
            si.Title = "产品质量证明书";
            hssfworkbook.SummaryInformation = si;
        }

        protected void Button_Save_Click(object sender, EventArgs e)
        {

            InitializeWorkbook();
            HSSFSheet sheet1 = hssfworkbook.GetSheet("产品质保书");
            sheet1.GetRow(6).GetCell(2).SetCellValue(this.RiQi.Text);
            Button_Query_Click(null, null);
            sheet1.GetRow(5).GetCell(2).SetCellValue(订货单位);
            sheet1.GetRow(6).GetCell(14).SetCellValue(序号);
            sheet1.GetRow(7).GetCell(1).SetCellValue(pinming);
            sheet1.GetRow(7).GetCell(10).SetCellValue("   " + 合同号);
            sheet1.GetRow(8).GetCell(1).SetCellValue(gangji);
            sheet1.GetRow(8).GetCell(4).SetCellValue(guige);
            sheet1.GetRow(8).GetCell(9).SetCellValue(总长度);

            sheet1.GetRow(9).GetCell(1).SetCellValue(总重量);
            sheet1.GetRow(9).GetCell(3).SetCellValue(总支数);
            sheet1.GetRow(9).GetCell(6).SetCellValue(总件数);
            string str = "";
            for (int i = 0; i < luhaoArray.Length; i++)
            {
                str += luhaoArray[i];
                sheet1.GetRow(12 + i).GetCell(0).SetCellValue(luhaoArray[i]);
                sheet1.GetRow(12 + i).GetCell(1).SetCellValue(C[i]);
                sheet1.GetRow(12 + i).GetCell(2).SetCellValue(Mn[i]);
                sheet1.GetRow(12 + i).GetCell(3).SetCellValue(Si[i]);
                sheet1.GetRow(12 + i).GetCell(4).SetCellValue(P[i]);
                sheet1.GetRow(12 + i).GetCell(5).SetCellValue(S[i]);
                sheet1.GetRow(12 + i).GetCell(6).SetCellValue(Cr[i]);
                sheet1.GetRow(12 + i).GetCell(7).SetCellValue(Ni[i]);
                sheet1.GetRow(12 + i).GetCell(8).SetCellValue(Cu[i]);
                sheet1.GetRow(12 + i).GetCell(9).SetCellValue(Mo[i]);
                sheet1.GetRow(12 + i).GetCell(10).SetCellValue(Al[i]);
                sheet1.GetRow(12 + i).GetCell(11).SetCellValue(Bi[i]);
                sheet1.GetRow(12 + i).GetCell(12).SetCellValue(Sn[i]);
                sheet1.GetRow(12 + i).GetCell(13).SetCellValue(Pb[i]);
                sheet1.GetRow(12 + i).GetCell(14).SetCellValue(As[i]);
                sheet1.GetRow(12 + i).GetCell(15).SetCellValue(Sb[i]);
                sheet1.GetRow(19 + i).GetCell(0).SetCellValue(luhaoArray[i]);
                sheet1.GetRow(19 + i).GetCell(1).SetCellValue(抗拉强度[i]);
                sheet1.GetRow(19 + i).GetCell(3).SetCellValue(屈服极限[i]);
                sheet1.GetRow(19 + i).GetCell(5).SetCellValue(伸长率[i]);
                sheet1.GetRow(19 + i).GetCell(7).SetCellValue(横向冲击[i]);
                sheet1.GetRow(19 + i).GetCell(9).SetCellValue(纵向冲击[i]);

            }

            sheet1.GetRow(8).GetCell(13).SetCellValue(str);
            sheet1.GetRow(9).GetCell(9).SetCellValue(chanpinbianhao.Text);
            sheet1.GetRow(17).GetCell(1).SetCellValue("拉伸试验(纵向)       Tensile Test(Longitudinal)");
            sheet1.GetRow(17).GetCell(7).SetCellValue("冲击试验 J    Inpact value");
            sheet1.GetRow(17).GetCell(11).SetCellValue("通径检验    Drifring");
            sheet1.GetRow(17).GetCell(12).SetCellValue("静水压试验   H.W.P");
            sheet1.GetRow(17).GetCell(13).SetCellValue("无损探伤     NDE");
            sheet1.GetRow(17).GetCell(14).SetCellValue("表面检验      Surface");
            sheet1.GetRow(17).GetCell(15).SetCellValue("尺寸          Demenition");

            sheet1.GetRow(18).GetCell(1).SetCellValue("抗拉强度(Mpa)       Ts");
            sheet1.GetRow(18).GetCell(3).SetCellValue("屈服极限(Mpa)       Ys");
            sheet1.GetRow(18).GetCell(5).SetCellValue("伸长率,A(%)          E.L");
            sheet1.GetRow(18).GetCell(7).SetCellValue("横向冲击 AK           Widthwise");
            sheet1.GetRow(18).GetCell(9).SetCellValue("纵向冲击 AK           Lengthwise");
            sheet1.GetRow(24).GetCell(0).SetCellValue("备注");
            sheet1.GetRow(25).GetCell(0).SetCellValue("地址：陕西省西安市枣园东路31(Add:No.31,Zaoyuan East Road,Xi’an,ShaanXi)                 邮编(P.C)：710077                     Emile:shi you cai liao @ 163.com                          电话(Tel)：(029)84622961           传真(Fax)：(029)84622961");
            //sheet1.GetRow(4).GetCell(2).SetCellValue(160000);
            //sheet1.GetRow(4).GetCell(3).SetCellValue(14200);
            //sheet1.GetRow(4).GetCell(4).SetCellValue(tao1[0] + tao2[0]);
            //sheet1.GetRow(4).GetCell(5).SetCellValue(tao1[1] + tao2[1]);
            //sheet1.GetRow(4).GetCell(6).SetCellValue(tao1[2] + tao2[2]);
            //sheet1.GetRow(5).GetCell(2).SetCellValue(8000);
            //sheet1.GetRow(5).GetCell(3).SetCellValue(7000);
            //sheet1.GetRow(5).GetCell(4).SetCellValue(tao1[0]);
            //sheet1.GetRow(5).GetCell(5).SetCellValue(tao1[1]);
            //sheet1.GetRow(5).GetCell(6).SetCellValue(tao1[2]);
            //sheet1.GetRow(6).GetCell(2).SetCellValue(8000);
            //sheet1.GetRow(6).GetCell(3).SetCellValue(7200);
            //sheet1.GetRow(6).GetCell(4).SetCellValue(tao2[0]);
            //sheet1.GetRow(6).GetCell(5).SetCellValue(tao2[1]);
            //sheet1.GetRow(6).GetCell(6).SetCellValue(tao2[2]);
            //sheet1.GetRow(7).GetCell(2).SetCellValue(20000);
            //sheet1.GetRow(7).GetCell(3).SetCellValue(1800);
            //sheet1.GetRow(7).GetCell(4).SetCellValue(you[0]);
            //sheet1.GetRow(7).GetCell(5).SetCellValue(you[1]);
            //sheet1.GetRow(7).GetCell(6).SetCellValue(you[2]);
            //sheet1.GetRow(8).GetCell(2).SetCellValue(180000);
            //sheet1.GetRow(8).GetCell(3).SetCellValue(7000);
            //sheet1.GetRow(8).GetCell(4).SetCellValue(tao1[0] + tao2[0] + you[0]);
            //sheet1.GetRow(8).GetCell(5).SetCellValue(tao1[1] + tao2[1] + you[1]);
            //sheet1.GetRow(8).GetCell(6).SetCellValue(tao1[2] + tao2[2] + you[2]);
            //sheet1.GetRow(12).GetCell(2).SetCellValue(450000);
            //sheet1.GetRow(12).GetCell(3).SetCellValue(30000);
            //sheet1.GetRow(12).GetCell(4).SetCellValue(zhi[0]);
            //sheet1.GetRow(12).GetCell(5).SetCellValue(zhi[1]);
            //sheet1.GetRow(12).GetCell(6).SetCellValue(zhi[2]);
            //sheet1.GetRow(13).GetCell(2).SetCellValue(220000);
            //sheet1.GetRow(13).GetCell(3).SetCellValue(32000);
            //sheet1.GetRow(13).GetCell(4).SetCellValue(zhi[3]);
            //sheet1.GetRow(13).GetCell(5).SetCellValue(zhi[4]);
            //sheet1.GetRow(13).GetCell(6).SetCellValue(zhi[5]);
            //sheet1.GetRow(14).GetCell(2).SetCellValue(350000);
            //sheet1.GetRow(14).GetCell(3).SetCellValue(38000);
            //sheet1.GetRow(14).GetCell(4).SetCellValue(liang[0]);
            //sheet1.GetRow(14).GetCell(5).SetCellValue(liang[1]);
            //sheet1.GetRow(14).GetCell(6).SetCellValue(liang[2]);
            //sheet1.GetRow(15).GetCell(2).SetCellValue(350000);
            //sheet1.GetRow(15).GetCell(3).SetCellValue(38000);
            //sheet1.GetRow(15).GetCell(4).SetCellValue(liang[3]);
            //sheet1.GetRow(15).GetCell(5).SetCellValue(liang[4]);
            //sheet1.GetRow(15).GetCell(6).SetCellValue(liang[5]);
            //string op = (string)this.Session["OperatorName"];
            //if (op != null)
            //{
            //    sheet1.GetRow(9).GetCell(5).SetCellValue(op);
            //}
            //sheet1.ForceFormulaRecalculation = true;
            WriteToFile();
            Response.End();
        }

        protected void Button_Query_Click(object sender, EventArgs e)
        {
            this.kbianhao.Text = "";
            String ckid = Request.QueryString["ckid"];
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            Cmd.CommandText = @"SELECT     dbo.产品出库单.序号, dbo.产品.品名, dbo.产品.钢级, dbo.产品.规格, dbo.产品销售合同.合同编号, dbo.产品出库单.总长度, dbo.产品出库单.总重量, 
                      dbo.产品出库单.总支数, dbo.产品出库单.总件数, dbo.产品订货单位.名称
                      FROM         dbo.产品出库单 INNER JOIN
                      dbo.产品销售合同 ON dbo.产品出库单.合同ID = dbo.产品销售合同.ID INNER JOIN
                      dbo.产品 ON dbo.产品出库单.产品类别ID = dbo.产品.ID INNER JOIN
                      dbo.产品订货单位 ON dbo.产品销售合同.订货单位ID = dbo.产品订货单位.ID where 产品出库单.ID=" + ckid;



            SqlDataReader Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                序号 = Reader.GetString(0);
                pinming = Reader.GetString(1);
                gangji = Reader.GetString(2);
                guige = Reader.GetString(3);
                合同号 = Reader.GetString(4);
                总长度 = Reader.GetDouble(5).ToString("0.00");
                总重量 = Reader.GetDouble(6).ToString("0.00");
                总支数 = Reader.GetInt32(7).ToString();
                总件数 = Reader.GetInt32(8).ToString();
                订货单位 = Reader.GetString(9);

            }
            Reader.Close();
            int 产品ID = 0;
            if (pinming == "油管")
            {
                产品ID = 1;
            }
            else
            {
                产品ID = 2;
            }
            Cmd.CommandText = @"SELECT DISTINCT 
                      dbo.产品库存.炉号, dbo.产品理化参数.C, dbo.产品理化参数.Mn, dbo.产品理化参数.Si, dbo.产品理化参数.P, dbo.产品理化参数.S, dbo.产品理化参数.Cr, dbo.产品理化参数.Ni, 
                      dbo.产品理化参数.Cu, dbo.产品理化参数.Mo, dbo.产品理化参数.Al, dbo.产品理化参数.V, dbo.产品理化参数.Bi, dbo.产品理化参数.Sn, dbo.产品理化参数.Pb, dbo.产品理化参数.[As], 
                      dbo.产品理化参数.Sb, dbo.产品理化参数.抗拉强度, dbo.产品理化参数.屈服极限, dbo.产品理化参数.伸长率, dbo.产品理化参数.横向冲击, dbo.产品理化参数.纵向冲击, 
                      dbo.产品理化参数.通径检验, dbo.产品理化参数.静水压试验, dbo.产品理化参数.无损探伤, dbo.产品理化参数.表面检验, dbo.产品理化参数.尺寸
                      FROM         dbo.产品库存 INNER JOIN
                      dbo.产品理化参数 ON dbo.产品库存.炉号 = dbo.产品理化参数.炉号 where 出库单ID=" + ckid + " and dbo.产品理化参数.产品ID like " + 产品ID;

            Reader = Cmd.ExecuteReader();


            while (Reader.Read())
            {
                炉号 += Reader.GetString(0) + "</br>";
                luhaoArray[count] = Reader.GetString(0);
                if (Reader.IsDBNull(1))
                {
                    C[count] = "—";
                }
                else
                {
                    C[count] = Reader.GetString(1);
                }
                if (Reader.IsDBNull(2))
                {
                    Mn[count] = "—";
                }
                else
                {
                    Mn[count] = Reader.GetString(2);
                }
                if (Reader.IsDBNull(3))
                {
                    Si[count] = "—";
                }
                else
                {
                    Si[count] = Reader.GetString(3);
                } if (Reader.IsDBNull(4))
                {
                    P[count] = "—";
                }
                else
                {
                    P[count] = Reader.GetString(4);
                }
                if (Reader.IsDBNull(5))
                {
                    S[count] = "—";
                }
                else
                {
                    S[count] = Reader.GetString(5);
                }
                if (Reader.IsDBNull(6))
                {
                    Cr[count] = "—";
                }
                else
                {
                    Cr[count] = Reader.GetString(6);
                }
                if (Reader.IsDBNull(7))
                {
                    Ni[count] = "—";
                }
                else
                {
                    Ni[count] = Reader.GetString(7);
                }
                if (Reader.IsDBNull(8))
                {
                    Cu[count] = "—";
                }
                else
                {
                    Cu[count] = Reader.GetString(8);
                }
                if (Reader.IsDBNull(9))
                {
                    Mo[count] = "—";
                }
                else
                {
                    Mo[count] = Reader.GetString(9);
                }
                if (Reader.IsDBNull(10))
                {
                    Al[count] = "—";
                }
                else
                {
                    Al[count] = Reader.GetString(10);
                }
                if (Reader.IsDBNull(11))
                {
                    V[count] = "—";
                }
                else
                {
                    V[count] = Reader.GetString(11);
                }
                if (Reader.IsDBNull(12))
                {
                    Bi[count] = "—";
                }
                else
                {
                    Bi[count] = Reader.GetString(12);
                }
                if (Reader.IsDBNull(13))
                {
                    Sn[count] = "—";
                }
                else
                {
                    Sn[count] = Reader.GetString(13);
                }
                if (Reader.IsDBNull(14))
                {
                    Pb[count] = "—";
                }
                else
                {
                    Pb[count] = Reader.GetString(14);
                }
                if (Reader.IsDBNull(15))
                {
                    As[count] = "—";
                }
                else
                {
                    As[count] = Reader.GetString(15);
                }
                if (Reader.IsDBNull(16))
                {
                    Sb[count] = "—";
                }
                else
                {
                    Sb[count] = Reader.GetString(16);
                }
                if (Reader.IsDBNull(17))
                {
                    抗拉强度[count] = "—";
                }
                else
                {
                    抗拉强度[count] = Reader.GetString(17);
                }
                if (Reader.IsDBNull(18))
                {
                    屈服极限[count] = "—";
                }
                else
                {
                    屈服极限[count] = Reader.GetString(18);
                }
                if (Reader.IsDBNull(19))
                {
                    伸长率[count] = "—";
                }
                else
                {
                    伸长率[count] = Reader.GetString(19);
                }
                if (Reader.IsDBNull(20))
                {
                    横向冲击[count] = "合格";
                }
                else
                {
                    横向冲击[count] = Reader.GetString(20);
                }
                if (Reader.IsDBNull(21))
                {
                    纵向冲击[count] = "合格";
                }
                else
                {
                    纵向冲击[count] = Reader.GetString(21);
                }
                if (Reader.IsDBNull(22))
                {
                    通径检验[count] = "合格";
                }
                else
                {
                    通径检验[count] = Reader.GetString(22);
                }
                if (Reader.IsDBNull(23))
                {
                    静水压试验[count] = "合格";
                }
                else
                {
                    静水压试验[count] = Reader.GetString(23);
                }
                if (Reader.IsDBNull(24))
                {
                    无损探伤[count] = "合格";
                }
                else
                {
                    无损探伤[count] = Reader.GetString(24);
                }
                if (Reader.IsDBNull(25))
                {
                    表面检验[count] = "合格";
                }
                else
                {
                    表面检验[count] = Reader.GetString(25);
                }
                if (Reader.IsDBNull(26))
                {
                    尺寸[count] = "合格";
                }
                else
                {
                    尺寸[count] = Reader.GetString(26);
                }
                count++;
            }

            Reader.Close();


            Cmd.CommandText = @"select 捆号 from 产品库存
                      where 出库单ID=" + ckid;

            Reader = Cmd.ExecuteReader();


            while (Reader.Read())
            {
                捆号[kunhaoCount] = Reader.GetString(0);
                kunhaoCount++;
            }
            Reader.Close();
            for (int i = 0; i < 捆号.Length; i++)
            {
                this.kbianhao.Text += 捆号[i];
            }
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();

        }
    }
}
